package com.subway.dao.Impl;

import com.subway.dao.BaseDao;
import com.subway.dao.SubWayDao;
import com.subway.entity.SubWayInfo;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 *  地铁信息访问接口 实现类
 */
public class SubWayDaoImpl extends BaseDao implements SubWayDao {
    private Connection conn;
    private PreparedStatement pstmt;
    private ResultSet rs;
    @Override
    public int add(SubWayInfo subWayInfo) {
        String sql = "INSERT into subwayinfo VALUES(0,?,?,?,?,?,?);";
        return executeUpdate(sql,
                subWayInfo.getSubwayname(),
                subWayInfo.getStartstation(),
                subWayInfo.getEndstation(),
                subWayInfo.getStationnum(),
                subWayInfo.getStarttime(),
                subWayInfo.getPrice());
    }

    @Override
    public int delete(int id) {
        String sql = "DELETE FROM subwayinfo WHERE id=?;";
        return executeUpdate(sql,id);
    }

    @Override
    public int update(SubWayInfo subWayInfo) {
        String sql = "UPDATE subwayinfo set subwayname=?,\n" +
                    "startstation=?,endstation=?,stationnum=?,starttime=?,price=? WHERE id = ?;";
        return executeUpdate(sql,
                subWayInfo.getSubwayname(),
                subWayInfo.getStartstation(),
                subWayInfo.getEndstation(),
                subWayInfo.getStationnum(),
                subWayInfo.getStarttime(),
                subWayInfo.getPrice(),
                subWayInfo.getId());
    }

    @Override
    public SubWayInfo getSubWayInfoById(int id) {
        String sql = "SELECT id,subwayname,startstation,endstation,stationnum,starttime,price \n" +
                    "FROM subwayinfo \n" +
                    "WHERE id=?;";
        SubWayInfo subWayInfo = null;
        try {
            conn = openConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,id);
            rs = pstmt.executeQuery();
            if(rs.next()){
                String subwayname = rs.getString("subwayname");
                String startstation = rs.getString("startstation");
                String endstation = rs.getString("endstation");
                int stationnum = rs.getInt("stationnum");
                String starttime = rs.getString("starttime");
                int price = rs.getInt("price");
                subWayInfo = new SubWayInfo(id,subwayname,startstation,endstation,stationnum,starttime,price);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            closeAll(rs,pstmt,conn);
        }
        return subWayInfo;
    }

    @Override
    public List<SubWayInfo> list() {
        String sql = "SELECT id,subwayname,startstation,endstation,stationnum,starttime,price FROM subwayinfo;";
        List<SubWayInfo> list = new ArrayList<>();
        try {
            conn = openConnection();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while(rs.next()){
                int id = rs.getInt("id");
                String subwayname = rs.getString("subwayname");
                String startstation = rs.getString("startstation");
                String endstation = rs.getString("endstation");
                int stationnum = rs.getInt("stationnum");
                String starttime = rs.getString("starttime");
                int price = rs.getInt("price");
                SubWayInfo subWayInfo = new SubWayInfo(id,subwayname,startstation,endstation,stationnum,starttime,price);
                list.add(subWayInfo);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            closeAll(rs,pstmt,conn);
        }
        return list;
    }
}
